package com.mdd.common.mapper.system;

import com.mdd.common.core.basics.IBaseMapper;
import com.mdd.common.entity.course.Course;
import com.mdd.common.entity.system.Coupon;
import com.mdd.common.vo.system.ConponVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;

/**
 * 优惠券Mapper
 * @author panweiliang
 */
@Mapper
public interface CouponMapper extends IBaseMapper<Coupon> {

    @Select({"SELECT COUNT(id) AS couponCount FROM ls_coupon WHERE status = #{status} AND delete_time IS NULL "})
    Long countByStatus(@Param("status") Integer status);

    @Select({"SELECT COUNT(id) AS couponCount FROM ls_coupon WHERE delete_time IS NULL "})
    Long countAll();

    @Select({"SELECT * FROM ls_coupon WHERE status = #{status} AND receiving_method = #{receivingMethod} AND delete_time IS NULL ORDER BY create_time desc "})
    List<Coupon> getByStatusAndReceivingMethod(@Param("status") Integer status, @Param("receivingMethod") Integer receivingMethod);

    @Select({"SELECT * FROM ls_coupon WHERE status = 2 AND receiving_method = 1 AND used = 1 AND delete_time IS NULL "})
    List<Coupon> getAllAvailable();

    @Select({"SELECT DISTINCT coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_course cc ON coupon.id = cc.coupon_id WHERE coupon.status = 2 AND coupon.receiving_method = 1 AND coupon.used = 2 AND cc.course_id = #{courseId} AND coupon.delete_time IS NULL "})
    List<Coupon> getCourseAvailable(@Param("courseId") Integer courseId);

    @Select({"SELECT DISTINCT coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_course cc ON coupon.id = cc.coupon_id WHERE coupon.status = 2 AND coupon.receiving_method = 1 AND coupon.used = 3 AND cc.course_id != #{courseId} AND coupon.delete_time IS NULL "})
    List<Coupon> getCourseUnavailable(@Param("courseId") Integer courseId);

    @Select({"SELECT t.* FROM (SELECT receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND used = 1 AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND receive.start_time <= #{now} AND receive.expire_time >= #{now}" +
            " UNION " +
            "SELECT  receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND coupon.used = 2 AND coupon.id IN (SELECT coupon_id FROM ls_coupon_course WHERE course_id = #{courseId}) AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND receive.start_time <= #{now} AND receive.expire_time >= #{now}" +
            " UNION " +
            "SELECT  receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND coupon.used = 3 AND coupon.id NOT IN (SELECT coupon_id FROM ls_coupon_course WHERE course_id = #{courseId}) AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND receive.start_time <= #{now} AND receive.expire_time >= #{now}) t WHERE (t.type = 3 OR t.full_amount <= #{amount}) ORDER BY t.amount DESC, t.receiveTime ASC "})
    List<ConponVo> getMineAllAvailable(@Param("courseId") Integer courseId, @Param("userId") Integer userId, @Param("amount") String amount, @Param("now") Long now);

    @Select({"SELECT t.* FROM (SELECT receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND used = 1 AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND (coupon.full_amount > #{amount} OR receive.start_time > #{now} OR receive.expire_time < #{now})" +
            " UNION " +
            "SELECT receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND coupon.used = 2 AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND (coupon.id IN (SELECT coupon_id FROM ls_coupon_course WHERE course_id != #{courseId}) OR receive.start_time > #{now} OR receive.expire_time < #{now})" +
            " UNION " +
            "SELECT DISTINCT receive.id AS receiveId, receive.receive_time AS receiveTime, receive.expire_time AS expireTime, receive.start_time AS receiveStartTime, coupon.* FROM ls_coupon coupon INNER JOIN ls_coupon_receive receive ON coupon.id = receive.coupon_id WHERE coupon.status = 2 AND coupon.used = 3 AND coupon.delete_time IS NULL AND receive.used_time IS NULL AND receive.status = 0 AND receive.user_id = #{userId} AND (coupon.id IN (SELECT coupon_id FROM ls_coupon_course WHERE course_id = #{courseId}) OR receive.start_time > #{now} OR receive.expire_time < #{now})) AS t ORDER BY t.amount DESC, t.receiveTime ASC"})
    List<ConponVo> getMineUnavailable(@Param("courseId") Integer courseId,  @Param("userId") Integer userId, @Param("amount") String amount, @Param("now") Long now);

}
